<?
    class Principio_Model_SpaMapper extends Principio_Library_Mapper
    {
        const PRV_ATTEMPT = 0;
        const PRV_XML_FAIL = 1;
        const PRV_OK = 2;
        const PRV_FIRMWARE_LOOKUP = 3;
        const PRV_FIRMWARE_FAIL = 4;
        const PRV_FIRMWARE_OK =5;

        private static $db_table = null;

        private static function getDBTable()
        {
            if (Principio_Model_SpaMapper::$db_table == null)
                self::$db_table = new Principio_Model_DbTable_Spa();

            return self::$db_table;
        }


        public static function deleteAllByType($type_id)
        {
            // Get all SPA with given type
            $select = self::getDBTable()->select()->where("type_id=?", $type_id);
            $spaes = self::getDBTable()->fetchAll($select);

            // Delete all accountlinks
            foreach ($spaes as $spa)
            {
                Principio_Model_AccountLinkMapper::delete($spa->id);
                Principio_Model_SpaExtTemplatesMapper::delete($spa->id);
            };

            // Delete SPA
            self::getDBTable()->delete(array("type_id=?" => $type_id));
        }


        public static function deleteAllInCompany($company_id)
        {
            // Get all SPA in the company
            $select = self::getDBTable()->select()->where("company_id=?", $company_id);
            $spaes = self::getDBTable()->fetchAll($select);

            // Delete all accountlinks
            foreach ($spaes as $spa)
            {
                Principio_Model_AccountLinkMapper::delete($spa->id);
                Principio_Model_SpaExtTemplatesMapper::delete($spa->id);
            };

            // Delete SPA
            self::getDBTable()->delete(array("company_id=?" => $company_id));

            // Delete Type Ext Template
            Principio_Model_CompanyTypeExtTemplateMapper::deleteAll($company_id);
        }


        public static function delete(Principio_Model_Spa $model_spa, $use_transaction = true)
        {
            try
            {
                if ($use_transaction)
                    self::getDBTable()->getAdapter()->beginTransaction();

                // Delete AccountLink
                Principio_Model_AccountLinkMapper::delete($model_spa->getID());

                // Delete SPA Ext Templates
                Principio_Model_SpaExtTemplatesMapper::delete($model_spa->getID());

                // Check if it is a last SPA with current type
                if (self::getTypeCount($model_spa->getModelType()->getID(), $model_spa->getCompanyID()) == 1)
                    // delete templates in that case
                    Principio_Model_CompanyTypeExtTemplateMapper::delete($model_spa->getModelType()->getID(), $model_spa->getCompanyID());

                // Delete SPA
                self::getDBTable()->delete(array("id=?" => intval($model_spa->getID())));

                if ($use_transaction)
                    self::getDBTable()->getAdapter()->commit();

                return true;
            }
            catch (Exception $ex)
            {
                $return = self::reportException($ex);

                if ($use_transaction)
                {
                    self::getDBTable()->getAdapter()->rollback();
                    return false;
                }
                else
                    return $return;

            };
        }


        public static function save(Principio_Model_Spa &$model_spa)
        {
            try
            {
                self::getDBTable()->getAdapter()->beginTransaction();

                $spa_id = $model_spa->getID();
                $data = array(
                        "mac" => $model_spa->getMAC(),
                        "serial" => $model_spa->getSerial(),
                        "ip" => $model_spa->getIP()
                    );

                if ($spa_id == -1)
                {
                    $data['company_id'] = $model_spa->getCompanyID();
                    $data['type_id'] = $model_spa->getTypeID();

                    $model_spa->setID(self::getDBTable()->insert($data));

                    // Create AccountLink
                    $model_accountlink = new Principio_Model_AccountLink($model_spa->getModelType()->getExts());
                    $model_accountlink->setSpaID($model_spa->getID());
                    for ($i = 0; $i < $model_spa->getModelType()->getExts(); $i++)
                        $model_accountlink->setLinkAccountID($i, $model_spa->getModelAccount($i)->getID());

                    Principio_Model_AccountLinkMapper::save($model_accountlink);

                    // Create SpaExtTemplate
                    $model_spa_ext_templates = new Principio_Model_SpaExtTemplates($model_spa->getModelType()->getExts());
                    $model_spa_ext_templates->setSpaID($model_spa->getID());
                    Principio_Model_SpaExtTemplatesMapper::insert($model_spa_ext_templates);

                    // Check if it is first SPA of its type
                    if (self::getTypeCount($model_spa->getModelType()->getID(), $model_spa->getCompanyID()) == 1)
                        // create templates in that case
                        Principio_Model_CompanyTypeExtTemplateMapper::insert($model_spa);
                }
                else
                {
                    self::getDBTable()->update($data, array("id=?" => $spa_id));
                    $model_accountlink = Principio_Model_AccountLinkMapper::getBySpaID($model_spa->getID());

                    for ($i = 0; $i < $model_spa->getModelType()->getExts(); $i++)
                        $model_accountlink->setLinkAccountID($i, $model_spa->getModelAccount($i)->getID());

                    Principio_Model_AccountLinkMapper::save($model_accountlink);
                };

                self::getDBTable()->getAdapter()->commit();

                return true;
            }
            catch (Exception $ex)
            {
                self::reportException($ex);
                self::getDBTable()->getAdapter()->rollback();

                return false;
            };
        }


        public static function get($id = null)
        {
            try
            {
                if (is_null($id))
                {
                    $spaes_raw = self::getDBTable()->fetchAll();
                    $spaes = array();
                    foreach ($spaes_raw as $spa)
                        $spaes[$spa->id] = new Principio_Model_Spa($spa->toArray());

                    return $spaes;
                }
                else
                {
                    $spa_raw = self::getDBTable()->find(intval($id));
                    if (is_null($spa_raw))
                        return false;
                    else
                    {
                        $model_type = Principio_Model_TypeMapper::get($spa_raw[0]['type_id']);
                        $model_spa = new Principio_Model_Spa($model_type, $spa_raw[0]->toArray());
                        $model_accountlink = Principio_Model_AccountLinkMapper::getBySpaID($model_spa->getID());

                        for ($i = 0; $i < $model_type->getExts(); $i++)
                            $model_spa->setModelAccount($i, Principio_Model_AccountMapper::get($model_accountlink->getLinkAccountID($i)));

                        return $model_spa;
                    };
                };
            }
            catch (Exception $ex)
            {
                return self::reportException($ex);
            };
        }


        public static function getEx($IP, $MAC, $serial)
        {
            try
            {
                $select = self::getDBTable()->select()->where("ip=?", $IP)->where("mac=?", $MAC)->where("serial=?", $serial);
                $spa_raw = self::getDBTable()->fetchRow($select);

                if (is_null($spa_raw))
                    return false;
                else
                {
                    $model_type = Principio_Model_TypeMapper::get($spa_raw->type_id);
                    $model_spa = new Principio_Model_Spa($model_type, $spa_raw->toArray());
                    $model_accountlink = Principio_Model_AccountLinkMapper::getBySpaID($model_spa->getID());

                    for ($i = 0; $i < $model_type->getExts(); $i++)
                        $model_spa->setModelAccount($i, Principio_Model_AccountMapper::get($model_accountlink->getLinkAccountID($i)));

                    return $model_spa;
                };
            }
            catch (Exception $ex)
            {
                return self::reportException($ex);
            };
        }


        public static function getByTypeInCompany($type_id, $company_id)
        {
            try
            {
                $select = self::getDBTable()->select()->order(array("ip DESC"))->where("type_id=?", $type_id)->where("company_id=?", $company_id);
                $spaes_raw = self::getDBTable()->fetchAll($select);
                $model_type = Principio_Model_TypeMapper::get($type_id);
                $spaes = array();
                foreach ($spaes_raw as $spa)
                    $spaes[$spa->id] = new Principio_Model_Spa($model_type, $spa->toArray());

                return $spaes;
            }
            catch (Exception $ex)
            {
                return self::reportException($ex);
            };
        }


        public static function getIpInUse($company_id, $cur_ip = null)
        {
            try
            {
                $select = self::getDBTable()->select()->where("company_id=?", $company_id);
                if (!is_null($cur_ip))
                    $select = $select->where("ip<>?", $cur_ip);

                return (self::getDBTable()->fetchAll($select)->toArray());
            }
            catch (Exception $ex)
            {
                return self::reportException($ex);
            };
        }


        public static function getIpNotInUse($company_id, $cur_ip = null)
        {


            $model_company = Principio_Model_CompanyMapper::get($company_id);
            $ip_in_use = array();

            if (($model_company->isCompanyInPool()) || ($model_company->isPool()))
            {
                if ($model_company->isCompanyInPool())
                    $model_company = Principio_Model_CompanyMapper::get($model_company->getOwnerPoolId());

                $model_ancestors = Principio_Model_CompanyMapper::getAncestors($model_company->getID());
                foreach ($model_ancestors as $id => $model_ancestor)
                    $ip_in_use = array_merge($ip_in_use, self::getIpInUse($id, $cur_ip));
            };

            $ip_in_use = array_merge($ip_in_use, self::getIpInUse($company_id, $cur_ip));

            $ip_in_use_long = array();
            foreach ($ip_in_use as $index => $ip)
                $ip_in_use_long[] = ip2long($ip['ip']);

            $ip_range_from_long = ip2long($model_company->getRangeFrom());
            $ip_range_to_long = ip2long($model_company->getRangeTo());

            $ip_not_in_use = array();

            $i = $ip_range_from_long;
            sort($ip_in_use_long);
            $k = 0;
            $p = 0;


            while ($i <= $ip_range_to_long)
            {
                if ($k < count($ip_in_use_long))
                    $p = $ip_in_use_long[$k++];
                else
                    $p = $ip_range_to_long + 1;

                while ($i < $p)
                {
                    $ip_not_in_use[] = long2ip($i);
                    $i++;
                };

                $i++;
            };

            return $ip_not_in_use;
        }


        public static function checkUnique(&$values, $field)
        {
            try
            {
                $select = self::getDBTable()->select()->where($field."=?", $values[$field])->where("id<>?", $values['id']);
                $result = self::getDBTable()->fetchAll($select);

                return (count($result) != 0) ? (Principio_Model_CompanyMapper::get($result[0]['company_id'])) : false;
            }
            catch (Exception $ex)
            {
                return self::reportException($ex);
            };
        }


        public static function getDifferentTypesID($company_id)
        {
            try
            {
                $select = self::getDBTable()->select()->distinct()->from(self::getDBTable(), "type_id")->where("company_id=?", $company_id);
                $spaes_raw = self::getDBTable()->fetchAll($select);
                $types = array();

                foreach ($spaes_raw as $spa)
                    $types[$spa->type_id] = Principio_Model_TypeMapper::get($spa->type_id);

                return $types;
            }
            catch (Exception $ex)
            {
                return self::reportException($ex);
            };
        }


        public static function getTypeCount($type_id, $company_id)
        {
            try
            {
                $select = self::getDBTable()->select()->from(self::getDBTable(), "COUNT(*) as num")->where("type_id=?", $type_id)->where("company_id=?", $company_id);
                $result = self::getDBTable()->fetchAll($select);

                return ($result[0]->num);
            }
            catch (Exception $ex)
            {
                return self::reportException($ex);
            };
        }


        public static function getInCompany($company_id)
        {
            try
            {
                $select = self::getDBTable()->select()->where("company_id=?", $company_id);
                $spaes = self::getDBTable()->fetchAll($select);

                $spa_array = array();
                foreach ($spaes as $spa)
                    $spa_array[] = self::get($spa->id);

                return $spa_array;
            }
            catch (Exception $ex)
            {
                return self::reportException($ex);
            };
        }


        public static function logProvisioning(Principio_Model_Spa $model_spa, $type)
        {
            switch ($type)
            {
                case self::PRV_ATTEMPT:
                    $data = array("last_prov_attempt" => new Zend_Db_Expr("NOW()"), "prov_status" => "Profile: generating");
                    break;

                case self::PRV_XML_FAIL:
                    $data = array("prov_status" => "Profile: FAIL", "last_prov_msg" => "Can't generate profile");
                    break;

                case self::PRV_OK:
                    $data = array("last_prov" => new Zend_Db_Expr("NOW()"), "prov_status" => "Profile: was sent", "last_prov_msg" => "Ok");
                    break;

                case self::PRV_FIRMWARE_LOOKUP:
                    $data = array("prov_status" => "Firmware: looking for firmware");
                    break;

                case self::PRV_FIRMWARE_FAIL:
                    $data = array("last_firmware" => "0 (ERROR)", "prov_status" => "Firmware: FAIL", "last_prov_msg" => "Requested fw [".$model_spa->getModelType()->getFirmware()."] wasn't found");
                    break;

                case self::PRV_FIRMWARE_OK:
                    $data = array("last_firmware" => $model_spa->getModelType()->getFirmware(), "prov_status" => "Firmware: was sent", "last_prov_msg" => "Ok");
                    break;

                default:
                    $data = array();
                    break;
            };

            self::getDBTable()->update($data, array("id=?" => $model_spa->getID()));
        }

        public static function search($string)
        {
            $search_fields = array(
                "mac" => "Mac",
                "serial" => "Serial number",
                "ip" => "IP"
            );
            $result = parent::searchInDB(self::getDBTable(), $search_fields, $string);
            foreach ($result as $res)
            {
                $model_search = new Principio_Model_Search($string, "Spa");
                $model_search->setId($res["id"])->setResultName($res["ip"]." [".$res["mac"]."]")->setOpenItemFunctionName("Spa")->setParentID(false);
                foreach ($res as $col_name => $value)
                {
                    if (strstr($value, $string) && in_array($col_name, array_keys($search_fields)))
                        $searchable_text[$search_fields[$col_name]] = $value;
                }
                $model_search->setResult($searchable_text);
                $search[$res["id"]] = $model_search;
            }
            return $search;
        }
    }
?>